﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DAO
{
    partial class DataProvider : DataTable
    {
        public const string StringConnection = "Data Source=ONEDREAM\SQLEXPRESS2008;Initial Catalog=QLKH;Integrated Security=True";

        #region Local Declaration
        private OleDbDataAdapter mDataAdapter;
        private string mStringSQL;
        private string mMyTableName;
        private static OleDbConnection mMyConnection;
        #endregion

        #region Property Declaration
        public static string StringDataBaseFile = "data/QLPMT.mdb";
        public string StringSQL
        {
            get
            {
                return mStringSQL;
            }
            set
            {
                mStringSQL = value;
            }
        }

        public string MyTableName
        {
            get
            {
                return mMyTableName;
            }
            set
            {
                mMyTableName = value;
            }
        }

        public static OleDbConnection MyConnection
        {
            get
            {
                return mMyConnection;
            }
            set
            {
                mMyConnection = value;
            }
        }
        #endregion

        #region Contruction
        public DataProvider()
            : base()
        {
        }

        public DataProvider(string pMyTableName)
            : base(pMyTableName)
        {
            mMyTableName = pMyTableName;
            ReadData();
        }

        public DataProvider(string pMyTableName, string pStringSQL)
            : base(pMyTableName)
        {
            mMyTableName = pMyTableName;
            mStringSQL = pStringSQL;
            ReadData();
        }
        #endregion

        #region Information
        private void ReadData()
        {
            if (mStringSQL == "")
            {
                mStringSQL = "SELECT * FROM " + mMyTableName;
            }

            if (mMyConnection == null)
            {
                mMyConnection = new OleDbConnection();
                mMyConnection.ConnectionString = StringConnection + "Data Source=" + StringDataBaseFile;
            }
            else
            {
                try
                {
                    mMyConnection.ConnectionString = StringConnection + "Data Source=" + StringDataBaseFile;
                }
                catch (Exception ex)
                {
                }
            }

            try
            {
                if (MyConnection == null) { return; }
                mDataAdapter = new OleDbDataAdapter(mStringSQL, mMyConnection);
                mDataAdapter.FillSchema(this, SchemaType.Mapped);
                mDataAdapter.Fill(this);

                mDataAdapter.SelectCommand.CommandText = "Select * FROM " + mMyTableName;
                OleDbCommandBuilder Bo_phat_sinh = new OleDbCommandBuilder(mDataAdapter);
            }
            catch (OleDbException ex)
            {

            }
        }

        public bool Save()
        {
            bool Result = true;
            try
            {
                mDataAdapter.Update(this);
                this.AcceptChanges();

            }
            catch (OleDbException ex)
            {
                this.RejectChanges();
                Result = false;
            }
            return Result;
        }

        public void Filter(string pCondition)
        {
            try
            {
                this.DefaultView.RowFilter = pCondition;
            }
            catch (Exception ex)
            {
            }
        }
        #endregion

        #region Query
        //thuc thi cau truy van cap nhat du lieu : them, xoa, sua
        public int Excute_Non_Query(string StringCommand)
        {
            try
            {
                OleDbCommand myCommand = new OleDbCommand(StringCommand, mMyConnection);
                if (MyConnection.State == ConnectionState.Closed)
                {
                    mMyConnection.Open();
                }
                int Result = myCommand.ExecuteNonQuery();
                mMyConnection.Close();
                return Result;
            }
            catch (OleDbException ex)
            {
                return -1;
            }
        }
        //thuc thi cau truy van lay du lieu: doc
        public object Excute_Query(string StringCommand)
        {
            try
            {
                OleDbCommand myCommand = new OleDbCommand(StringCommand, mMyConnection);
                if (MyConnection.State == ConnectionState.Closed)
                {
                    mMyConnection.Open();
                }
                object Result = myCommand.ExecuteScalar();
                mMyConnection.Close();
                return Result;
            }
            catch (OleDbException ex)
            {
                return null;
            }
        }
        //tim 1 truong du lieu trong 1 bang 
        public object Dlookup(string pFieldName, string pTableName, string pCondition)
        {
            try
            {
                string StringCommand = "Select " + pFieldName + " as ResultField from " + pTableName + " where " + pCondition;
                OleDbCommand myCommand = new OleDbCommand(StringCommand, mMyConnection);
                if (MyConnection.State == ConnectionState.Closed)
                {
                    mMyConnection.Open();
                }
                object Result = myCommand.ExecuteScalar();
                mMyConnection.Close();
                return Result;
            }
            catch (OleDbException ex)
            {
                return null;
            }
        }
        public object DCount(string pFieldName, string pTableName, string pCondition)
        {
            try
            {
                string StringCommand = "Select Count(" + pFieldName + ") as ResultField from " + pTableName + " where " + pCondition;
                OleDbCommand myCommand = new OleDbCommand(StringCommand, mMyConnection);
                if (MyConnection.State == ConnectionState.Closed)
                {
                    mMyConnection.Open();
                }
                object Result = myCommand.ExecuteScalar();
                mMyConnection.Close();
                return Result;
            }
            catch (OleDbException ex)
            {
                return 0;
            }
        }
        #endregion
        #region Event Opration
        private void mDataAdapter_RowUpdated(object sender, System.Data.OleDb.OleDbRowUpdatedEventArgs e)
        {
            try
            {
                if (this.PrimaryKey.Length > 0 && this.PrimaryKey[0].AutoIncrement == true)
                {
                    if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert)
                    {
                        //doc id dung autonumber vua moi them vao csdl len(dung cho phi ket noi)
                        OleDbCommand cmd = new OleDbCommand("Select @@IDENTITY", mMyConnection);
                        e.Row[0] = cmd.ExecuteScalar();
                        e.Row.AcceptChanges();
                    }
                }
            }
            catch (Exception ex)
            {

            }
        }
        #endregion
    }
}
